from flask import Flask, render_template, redirect, request, url_for, send_file
# import pymssql
import pandas as pd
import geopandas as gpd
from datetime import  timedelta
from gcj02_to_wgs84 import gcj02_to_wgs84

app = Flask(__name__)

@app.route('/')
def index():
    return render_template('login.html')

@app.route('/Foot', methods=['POST', 'GET'])
def Foot():
    return render_template('Foot.html')

@app.route('/POI_SELECT', methods=['POST', 'GET'])
def POI():
    from urllib.request import urlopen
    from urllib.parse import quote
    import geopandas as gpd
    import pandas as pd
    import json
    import string
    KeyWords = [request.form["KeyWords"]]
    regions = [request.form["regions"]]
    def create_json(url):
        url = quote(url, safe=string.printable)
        url_file = urlopen(url)
        json_file = url_file.read()
        json_dict = json.loads(json_file)
        return json_dict

    def read_json(json_dict, pois_list):
        for text in json_dict["pois"]:
            if text["pname"] == "湖北省":
                poi_list = []
                poi_list.append(text["name"])
                loc = str(text["location"]).split(',')
                poi_list.append(float(loc[0]))
                poi_list.append(float(loc[1]))
                poi_list.append(text["adname"])
                if "address" in text:
                    poi_list.append(str(text["address"]))
                else:
                    poi_list.append("未记录地址")
                pois_list.append(poi_list)
        return pois_list

    ak = "1f1810c6114d9b1ea96780145ca2ee2e"
    page_size = 20;
    page_num = 0;
    scope = 1
    for KeyWord in KeyWords:
        for region in regions:
            pois_list = []
            url = "https://restapi.amap.com/v3/place/text?key=" + str(ak) + "&keywords=" + str(
                KeyWord) + "&city=" + str(
                region) + "&children=1&citylimit=true&offset=20&page=1&output=json&extensions=all"
            json_dict = create_json(url)
            total = int(json_dict["count"])
            page_nums = int(total / 20)
            if total % 20 > 0:
                page_nums += 1
            for page_num in range(0, page_nums):
                url = "https://restapi.amap.com/v3/place/text?key=" + str(ak) + "&keywords=" + str(
                    KeyWord) + "&city=" + str(region) + "&children=1&citylimit=true&offset=20&page=" + str(
                    page_num) + "&output=json&extensions=all"
                json_dict = create_json(url)
                read_json(json_dict, pois_list)
    df = pd.DataFrame(pois_list, columns=["Name", "Longitude", "Latitude", "Region", "Address"])
    df[['Longitude', 'Latitude']] = df[['Longitude', 'Latitude']].apply(gcj02_to_wgs84, axis=1, result_type="expand")
    gdf = gpd.GeoDataFrame(df, geometry=gpd.points_from_xy(df.Longitude, df.Latitude))
    from keplergl import KeplerGl
    map_1 = KeplerGl()
    geojson = gdf.to_json()
    config = {'version': 'v1',
              'config': {'visState': {'filters': [],
                                      'layers': [],
                                      'interactionConfig': {'tooltip': {'fieldsToShow': {},
                                                                        'compareMode': False,
                                                                        'compareType': 'absolute',
                                                                        'enabled': True},
                                                            'brush': {'size': 0.5, 'enabled': False},
                                                            'geocoder': {'enabled': False},
                                                            'coordinate': {'enabled': False}},
                                      'layerBlending': 'normal',
                                      'splitMaps': [],
                                      'animationConfig': {'currentTime': None, 'speed': 1}},
                         'mapState': {'bearing': 0,
                                      'dragRotate': False,
                                      'latitude': 30.077763934564565,
                                      'longitude': 116.24472813365445,
                                      'pitch': 0,
                                      'zoom': 4.477013582260098,
                                      'isSplit': False},
                         'mapStyle': {'styleType': 'muted',
                                      'topLayerGroups': {},
                                      'visibleLayerGroups': {'label': True,
                                                             'road': True,
                                                             'border': False,
                                                             'building': True,
                                                             'water': True,
                                                             'land': True,
                                                             '3d building': False},
                                      'threeDBuildingColor': [224.4071295378559,
                                                              224.4071295378559,
                                                              224.4071295378559],
                                      'mapStyles': {}}}}
    map_1.add_data(data=geojson, name='geojson')
    map_1.save_to_html(data={'data_1': geojson}, config=config, file_name='static/POI_kepler.html')
    gdf= gdf.iloc[:,:-1]
    gdf.to_html('static/POI_table.html')
    return render_template('POI_Result.html')

@app.route('/SQL_SELECT', methods=['POST', 'GET'])
def SQL():
    SELECT = request.form["SELECT"]
    FROM = request.form["FROM"]
    WHERE = request.form["WHERE"]
    import psycopg2
    # database，user，password，host，port分别对应要连接的PostgreSQL数据库的数据库名、数据库用户名、用户密码、主机、端口信息
    conn = psycopg2.connect(database="test2", user="postgres", password="cxy010034", host="127.0.0.1", port="5432")
    if WHERE != '':
        sql = 'select ' + str(SELECT) + ' from ' + str(FROM) + ' where ' + str(WHERE) + ''
    else:
        sql = 'select ' + str(SELECT) + ' from ' + str(FROM) + ''
    gdf = gpd.GeoDataFrame.from_postgis(sql, con=conn, geom_col='geom')

    from keplergl import KeplerGl
    map_1 = KeplerGl()
    geojson = gdf.to_json()
    config = {'version': 'v1',
              'config': {'visState': {'filters': [],
                                      'layers': [],
                                      'interactionConfig': {'tooltip': {'fieldsToShow': {},
                                                                        'compareMode': False,
                                                                        'compareType': 'absolute',
                                                                        'enabled': True},
                                                            'brush': {'size': 0.5, 'enabled': False},
                                                            'geocoder': {'enabled': False},
                                                            'coordinate': {'enabled': False}},
                                      'layerBlending': 'normal',
                                      'splitMaps': [],
                                      'animationConfig': {'currentTime': None, 'speed': 1}},
                         'mapState': {'bearing': 0,
                                      'dragRotate': False,
                                      'latitude': 30.077763934564565,
                                      'longitude': 116.24472813365445,
                                      'pitch': 0,
                                      'zoom': 4.477013582260098,
                                      'isSplit': False},
                         'mapStyle': {'styleType': 'muted',
                                      'topLayerGroups': {},
                                      'visibleLayerGroups': {'label': True,
                                                             'road': True,
                                                             'border': False,
                                                             'building': True,
                                                             'water': True,
                                                             'land': True,
                                                             '3d building': False},
                                      'threeDBuildingColor': [224.4071295378559,
                                                              224.4071295378559,
                                                              224.4071295378559],
                                      'mapStyles': {}}}}
    map_1.add_data(data=geojson, name='geojson')
    map_1.save_to_html(data={'data_1': geojson}, config=config, file_name='static/SQL_kepler.html')
    gdf= gdf.iloc[:,:-1]
    gdf.to_html('static/SQL_table.html')
    return render_template('SQL_Result.html')


@app.route('/success')
def success():
    return render_template('SQL_POI.html')


@app.route('/login', methods=['POST', 'GET'])
def login():
    if request.method == 'POST':
        user = request.form['nm']
        return redirect(url_for('success', name=user))
    else:
        user = request.args.get('nm')
        return redirect(url_for('success', name=user))


if __name__ == '__main__':
    app.run(debug=True)
